import pandas as pd
from sqlalchemy import create_engine
from datetime import datetime
import openpyxl

engine = create_engine('mysql+pymysql://jsbi:jsbi-1701@47.114.55.19:9011/biv1?charset=utf8')
con = engine.connect()

# 宽表中增加了品类字段，我们先把品类字段更新到冗余字段中，加快分析
# sqlCmd = " select * from 财务_拼多多月度订单明细 where 交易时间='5月' and 店铺='拼多多-第一森林旗舰店' limit 20000 "
# sqlCmd = " select 店铺,平台商品id,新金额 from 财务_拼多多月度订单明细_debug"
sqlLoop = "select distinct 商品ID,分类 from 平台_拼多多各店铺id分类 "
sqlLoop = "select distinct 商品ID,分类 from 平台_拼多多各店铺id分类 where 店铺 is null "
dfLoop = pd.read_sql(sql=sqlLoop, con=engine)
for index, row in dfLoop.iterrows():
    #
    if row['商品ID'] is not None and row['分类'] is not None:
        engine.execute(
            " update 财务_拼多多月度订单明细 set 品类='" + row['分类'] + "' where 平台商品ID='" + row['商品ID'] + "' ")
        print("更新了一个产品的品类")
